﻿CREATE TABLE [CA].[GoodsDocLines] (
    [Id]            INT             NOT NULL,
    [DepartmentId]  AS              ([Id]/(100000000)),
    [DocId]         INT             NOT NULL,
    [PartyId]       INT             NULL,
    [Qntty]         INT             NOT NULL,
    [RetailSum]     DECIMAL (19, 2) NOT NULL,
    [ReturnLineId]  INT             NULL,
    [Note]          VARCHAR (250)   NULL,
    [MD]            DATETIME        CONSTRAINT [DF__GoodsDocLine__MD__405A880E] DEFAULT (getdate()) NOT NULL,
    [MW]            VARCHAR (50)    CONSTRAINT [DF__GoodsDocLine__MW__414EAC47] DEFAULT (suser_sname()) NOT NULL,
    [DiscountValue] TINYINT         NULL,
    CONSTRAINT [PK_GoodsDocLines] PRIMARY KEY NONCLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80),
    CONSTRAINT [FK_GoodsDocLines_Documents] FOREIGN KEY ([DocId]) REFERENCES [CA].[Documents] ([Id]) ON UPDATE CASCADE,
    CONSTRAINT [FK_GoodsDocLines_Parties] FOREIGN KEY ([PartyId]) REFERENCES [CD].[Parties] ([Id]) NOT FOR REPLICATION
);


GO
CREATE NONCLUSTERED INDEX [IX_GoodsDocLines_PartyId]
    ON [CA].[GoodsDocLines]([PartyId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_GoodsDocLines_ReturnLineId]
    ON [CA].[GoodsDocLines]([ReturnLineId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_GoodsDocLines_DocId]
    ON [CA].[GoodsDocLines]([DocId] ASC)
    INCLUDE([Qntty], [PartyId]);


GO
CREATE NONCLUSTERED INDEX [IX_GoodsDocLines_DepartmentId]
    ON [CA].[GoodsDocLines]([DepartmentId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_GoodsDocLines_MD]
    ON [CA].[GoodsDocLines]([MD] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_GoodsDocLines_Qntty]
    ON [CA].[GoodsDocLines]([Qntty] ASC);


GO
-- =============================================
-- Author:		Черноусов ОП
-- Create date: 2006,05,12
-- Description:	Пересчет остатков по партии
-- =============================================
CREATE TRIGGER [CA].[RecalcPartyRestAfterUpdate]
   ON CA.GoodsDocLines
   AFTER UPDATE 
AS 
BEGIN
	SET NOCOUNT ON;

	IF UPDATE(Qntty) OR UPDATE(PartyId)
	BEGIN

		UPDATE R SET R.RestQntty = R.RestQntty + T.SumQntty,
		R.MD = getdate(), R.MW = suser_sname()
		FROM CD.Parties AS R INNER JOIN (
			SELECT PartyId, sum(SumQntty) as SumQntty FROM (
				SELECT PartyId, sum(0 - Qntty) as SumQntty FROM deleted GROUP BY PartyId
				UNION ALL
				SELECT PartyId, sum(Qntty) as SumQntty FROM inserted GROUP BY PartyId
			) AS G GROUP BY G.PartyId
		) AS T ON R.Id = T.PartyId 

	END 

END

GO
-- =============================================
-- Author:		Черноусов ОП
-- Create date: 2006,05,12
-- Description:	Пересчет остатков по партии
-- =============================================
CREATE TRIGGER [CA].[RecalcPartyRestAfterDelete]
   ON CA.GoodsDocLines
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

UPDATE R SET R.RestQntty = R.RestQntty - T.SumQntty,
R.MD = getdate(), R.MW = suser_sname()
FROM CD.Parties AS R INNER JOIN (
SELECT PartyId, sum(Qntty) as SumQntty FROM deleted GROUP BY PartyId
) AS T ON R.Id = T.PartyId

END

GO
-- =============================================
-- Author:		Черноусов ОП
-- Create date: 2006,05,12
-- Description:	Пересчет остатков по партии
-- =============================================
CREATE TRIGGER [CA].[RecalcPartyRestAfterInsert]
   ON CA.GoodsDocLines
   AFTER INSERT 
AS 
BEGIN
	SET NOCOUNT ON;

UPDATE R SET R.RestQntty = R.RestQntty + T.SumQntty,
R.MD = getdate(), R.MW = suser_sname()
FROM CD.Parties AS R INNER JOIN (
SELECT PartyId, sum(Qntty) as SumQntty FROM inserted GROUP BY PartyId
) AS T ON R.Id = T.PartyId

END

GO

CREATE TRIGGER CA.GoodsDocLinesDeleteLog ON CA.GoodsDocLines AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

INSERT INTO [PR].[DocDeleteLog] (TableName, IdValue, MD, MW)
SELECT 'CA.GoodsDocLines', Id, getdate(), SYSTEM_USER FROM DELETED

END

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'идентификатор партии', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsDocLines', @level2type = N'COLUMN', @level2name = N'PartyId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'>0 - приход, <0 расход', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsDocLines', @level2type = N'COLUMN', @level2name = N'Qntty';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Сумма в розничных ценах >0 - приход, <0 расход', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsDocLines', @level2type = N'COLUMN', @level2name = N'RetailSum';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Код строки документа, для которой текущяя строка является возвратной', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsDocLines', @level2type = N'COLUMN', @level2name = N'ReturnLineId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'для строки чеков - размер скидки в процентах', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'GoodsDocLines', @level2type = N'COLUMN', @level2name = N'DiscountValue';

